﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Collections.ObjectModel;

namespace OrganizeYourSearch
{
    public class TagsDataProvider
    {
        string dbConnection;
        long maxFreq, minFreq;   //Max and min Freq of occurance of Tags

        /// <summary>
        /// Retrun freq of max occuring tag
        /// </summary>
        public long MaxFreq
        {
            get { return maxFreq; }
            set { maxFreq = value; }
        }

        /// <summary>
        /// Freq of the min occuring tag
        /// </summary>
        public long MinFreq
        {
            get { return minFreq; }
            set { minFreq = value; }
        }


        /// <summary>
        /// Default Constructor
        /// </summary>
        public TagsDataProvider()
        {
            dbConnection = "Data Source=|DataDirectory|OrgYrSrch.s3db";
            try
            {
                maxFreq= (long)ExecuteScalar("select max(count) as max from (select count(TagId)  as count from TagInstanceDetails group by TagId)"); 
                minFreq=(long)ExecuteScalar("select min(count) as min from (select count(TagId)  as count from TagInstanceDetails group by TagId)");
               
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        //
        /// <summary>
        /// SQL to run the datatable containing the result set
        /// </summary>
        /// <param name="tagNamePart">Part of the Tag Word</param>
        /// <returns>All the tags contating the string passed</returns>
        public ObservableCollection<Tag> GetTags(string tagNamePart)
        {

            ObservableCollection<Tag> Tags = new ObservableCollection<Tag>();
            try
            {
                SQLiteConnection cnn;
                cnn = new SQLiteConnection(dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = string.Format("select TagList.TagId,TagName,CurFreq from TagList, (select TagId,count(TagId) as CurFreq from TagInstanceDetails group by TagId) as CountTable where TagList.TagId=CountTable.TagId and TagList.TagName like \"%{0}%\"", tagNamePart);
                /*
                 The Actual Query:
                 select TagList.TagId,TagName,CurFreq 
                    from TagList,
                        (select TagId,count(TagId) as CurFreq from TagInstanceDetails group by TagId) as CountTable
                    where TagList.TagId=CountTable.TagId
                 * */
                SQLiteDataReader reader = null;
                reader=    mycommand.ExecuteReader();
                Tags.Clear();
                while (reader.Read())
                {
                    Tag tag = new Tag(
                        (long)reader["TagId"],
                        (long)reader["CurFreq"],
                        (string)reader["TagName"],
                        maxFreq,
                        minFreq
                        );
                    //Add to collection
                    Tags.Add(tag);
                }

                reader.Close();
                cnn.Close();

            }
            catch (Exception ex)
            {
                throw new Exception("From SQLiteDatabase.GetTags()\n" + ex.Message);
            }
            finally
            {
            }
            return Tags;

        }

      

        /// <summary>
        ///  Get Instances of the Tags with the given TagId
        /// </summary>
        /// <param name="tagId">Primary Key of the Tag</param>
        /// <returns>Detials of all the Instances where the selected Tag word were found</returns>
        public ObservableCollection<TagInstanceDetail> GetTagInstances(long tagId)
        {
            ObservableCollection<TagInstanceDetail> TagInstances = new ObservableCollection<TagInstanceDetail>();
            try
            {
                SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = string.Format("select * from TagInstanceDetails where TagId={0}", tagId);
                SQLiteDataReader reader = mycommand.ExecuteReader();
                TagInstances.Clear();
                while (reader.Read())
                {
                    TagInstanceDetail tagInstance = new TagInstanceDetail
                        (
                            (long)reader["InstanceId"],
                            (long)reader["TagId"],
                            (long)reader["StartPoint"],
                            (long)reader["EndPoint"],
                            (string)reader["Location"],
                            (string)reader["DocTitle"],
                            (string)reader["Description"]
                        );

                    //Add to collection
                    TagInstances.Add(tagInstance);
                }
                reader.Close();
                cnn.Close();
            }
            catch (Exception ex)
            {
                throw (new Exception("From SQLiteDatabase.GetTagInstances()\n"
                    + ex.Message));
            }
            return TagInstances;
        }

        /// <summary>
        /// Find TagId from TagName
        /// </summary>
        /// <param name="TagName">The word used as a tag</param>
        /// <returns>Primary Key of the TagWord from the database</returns>
        public long SelectTagId(string TagName)
        {
            long i=0;
            try
            {
                SQLiteConnection cnn = new SQLiteConnection(dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText = string.Format("select * from TagList where TagName={0}", TagName);
                SQLiteDataReader reader = mycommand.ExecuteReader();
                reader.Close();
                cnn.Close();
                
                while (reader.Read())
                {
                    i = (long)reader["TagId"];
                }
               
            }
            catch (Exception ex)
            {
                throw (new Exception("From SQLiteDatabase.GetTagInstances()\n"
                    + ex.Message));
            }
            return i;
        }

        /// <summary>
        /// Inserts a Tag Object into corresponding table
        /// </summary>
        /// <param name="Tagname">A string to be tagged</param>
        /// <returns>TagId of the inserted Tag</returns>
        public long InsertIntoTags(string Tagname)
        {
            try
            {
                string str = string.Format("insert into TagList (TagName) values (\"{0}\")",Tagname);
                this.ExecuteNonQuery(str);
                str = string.Format("select TagId from TagList where TagName = '{0}'", Tagname);
                long tagId=(long)this.ExecuteScalar(str);
                return tagId;
            }
            catch (Exception ex)
            {
                throw (new Exception("From SQLiteDatabase.GetTagInstances()\n"
                    + ex.Message));
               
            }
        }

        /// <summary>
        /// Inserts a TagInstanceDetail into the Database table
        /// </summary>
        /// <param name="curInstance">TagInstanceDetail Object</param>
        public void InsertIntoTagInstanceDetails(TagInstanceDetail curInstance)
        {
            try
            {
                string str = string.Format("insert into TagInstanceDetails (TagId, Location,DocTitle,Description,StartPoint,EndPoint) values ({0},\"{1}\",\"{2}\",\"{3}\",{4},{5})", 
                    curInstance.TagId,curInstance.Location,curInstance.DocTitle,curInstance.Description,curInstance.StartPoint,curInstance.EndPoint);
                this.ExecuteNonQuery(str);        
                
            }
            catch (Exception ex)
            {
                throw (new Exception("From SQLiteDatabase.GetTagInstances()\n"
                    + ex.Message));
            }
        }


        // ExecuteNonQuery: Following allows to interact with database for purpose other than executing query
        /// <summary>
        /// SQL to be run: to find the no of rows updated
        /// </summary>
        /// <param name="tagId"> Primary Key of TagInstanceDetails Table</param>
        /// <returns> Number of Updated Rows</returns>
        public int ExecuteNonQuery(int tagId)
        {
            SQLiteConnection conn = new SQLiteConnection(dbConnection);
            conn.Open();
            SQLiteCommand mycommand = new SQLiteCommand(conn);
            mycommand.CommandText = "select * from TagInstanceDetails where TagId=" + tagId.ToString();
            int numUpdatedRows = mycommand.ExecuteNonQuery();
            return numUpdatedRows;
        }

        // ExecuteNonQuery: Following allows to interact with database for purpose other than executing query
        
        /// <summary>
        /// SQL to be run: to find the no of rows updated
        /// </summary>
        /// <param name="str">Command Text of the Query</param>
        /// <returns> Number of Updated Rows</returns>
        public int ExecuteNonQuery(string str)
        {
            SQLiteConnection conn = new SQLiteConnection(dbConnection);
            conn.Open();
            SQLiteCommand mycommand = new SQLiteCommand(conn);
            mycommand.CommandText = str;
            int numUpdatedRows = mycommand.ExecuteNonQuery();
            return numUpdatedRows;
        }

        // ExecuteScalar: To retrieve single item from the query
        // 
        /// <summary>
        /// SQL to retrieve a string
        /// </summary>
        /// <param name="sql">SQL Query Command String</param>
        /// <returns>Single Object Value retruned from query</returns>
        public object ExecuteScalar(string sql)
        {
            SQLiteConnection cnn = new SQLiteConnection(dbConnection);
            cnn.Open();
            SQLiteCommand mycommand = new SQLiteCommand(cnn);
            mycommand.CommandText = sql;
            object value = mycommand.ExecuteScalar();
            value=(value==DBNull.Value?(long)0:value);
            return value;

        }

    }
}
